﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data;
using System.Runtime.Serialization.Formatters.Binary;
using ConfigLibrary;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace wmdata
{
    public class mysqlconn : Form
    {
        public mysqlconn()
        {
            Stream s;
            #if (DEBUG)
            {s = File.Open(Properties.Settings.Default.CONFIGDEV, FileMode.Open);}

            #elif! (DEBUG)            
            {s = File.Open(Properties.Settings.Default.CONFIGDEV, FileMode.Open);}
            #endif
            BinaryFormatter b = new BinaryFormatter();
            config = (Config)b.Deserialize(s);
            s.Close();
            config.GetSqlInfo(out _host, out _username, out _pass, out _database, out _port);
        }

        public void connect(MySqlConnection connection)
        {
            conn = connection;
            string[] database = _database.Split(new Char[] { ' ', ',' });
            conn.ConnectionString = "Database=" + database[2] + ";Data Source=" + _host + ";Port=" + _port + ";User Id=" + _username + ";Password=" + _pass;            
        }
           
        public void loadtables(string table)
        {
            command.CommandText = "Select * from " + table;
            command.Connection = conn;
            adapter.SelectCommand = command;
            adapter.TableMappings.Add(table, table);
            adapter.FillSchema(ds, SchemaType.Source, table);
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            adapter.Fill(ds, table);
            command.ExecuteNonQuery();
        }

        public void updatetables(DataTable table)
        {
            builder = new MySqlCommandBuilder(adapter);
            update = table.GetChanges();
            command.CommandText = "Select * from p";
            command.Connection = conn;
            adapter.SelectCommand = command;
            if (update != null)
            {
                adapter.Update(update);
            }
        }

        public bool CheckRecordIdExists(string record)
        {
            command = new MySqlCommand("Select @record from wmdata where timestamp = (Select max(timestamp) from wmdata);");
            wmdrow = MySqlHelper.ExecuteDataRow(conn.ConnectionString, command.CommandText, command.Parameters.AddWithValue("record", record));
            if (row != null) return true;
            else if (row == null) return false;
            else throw new Exception();     
        }

        public void AddRecordId(string record)
        {                   
            command.CommandText = "Insert into wmdata (id, record_id) values (id, " + record + ") where id = (Select max(id) from wmdata);";
            command.ExecuteNonQuery();
        }

        public int CheckRecordExists(string tab, string col, string val)
        {
            int id;
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                connect(conn);
            }            
            command = new MySqlCommand("SELECT * FROM " + tab + " WHERE " + col + " = '" + val + "'");
            DataRow dr = MySqlHelper.ExecuteDataRow(conn.ConnectionString, command.CommandText);           
            if (dr == null) return id = 0;
            else return id = (int)dr["id"];            
        }

        public long AddRecord(string tab, string col, string val)
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            command = new MySqlCommand("INSERT INTO " + tab + " (" + col + ") VALUES ('" + val + "')", conn);
            command.ExecuteNonQuery();
            long id = command.LastInsertedId;
            conn.Close();
            return id;
        }

        public int CheckRecordExistsProgYr(string tab, string[,] py)
        {
            int id;
            StringBuilder sb = new StringBuilder("SELECT * FROM ");
            if (conn.State == ConnectionState.Open) conn.Close();
            if (!String.IsNullOrEmpty(py[1, 0])) sb.Append(tab + " WHERE " + py[0,0] + " = '" + py[1,0] + "'");
            if (!String.IsNullOrEmpty(py[1, 1])) sb.Append("AND " + py[0,1] + " = '" + py[1,1] + "'");
            command = new MySqlCommand(sb.ToString()); 
            DataRow dr = MySqlHelper.ExecuteDataRow(conn.ConnectionString, command.CommandText);
            if (dr == null) return id = 0;
            else return id = (int)dr["id"];
        }

        public int AddRecordProgYr(string tab, string[,] py)
        {
            StringBuilder sb = new StringBuilder("INSERT INTO " + tab + " ");
            if (conn.State == ConnectionState.Closed) conn.Open();
            if (String.IsNullOrEmpty(py[1, 0])) py[1, 0] = "NULL";
            if (String.IsNullOrEmpty(py[1, 1])) py[1, 1] = "NULL";
            sb.Append("(" + py[0, 0] + ", " + py[0, 1] + ") VALUES ('" + py[1, 0] + "', " + py[1,1] + ")");            
            command = new MySqlCommand(sb.ToString(), conn);          
            command.ExecuteNonQuery();
            int id = (int)command.LastInsertedId;
            return id;
        }        

        public bool CheckLabelExists(string lab, string tab, string col)
        {         
            command = new MySqlCommand("SELECT * FROM " + tab + " WHERE " + col + " = '" + lab + "';");
            row = MySqlHelper.ExecuteDataRow(conn.ConnectionString, command.CommandText, command.Parameters.AddWithValue("lab", lab));
            if (row != null)
            {
               return true;
            }
            else if (row == null)
            {
                return false;
            }
            else throw new Exception();
        }

        public void AddLabelName(string lab)
        {
            command.CommandText = "Select row from label where id = (Select max(id) from label);";
            command.ExecuteNonQuery();
            int id = (int)command.LastInsertedId;
            command.CommandText = "Insert into label (id, name) values (" + id + ", " + lab + ");";
            command.ExecuteNonQuery();
        }

        object j;
        public DataRow wmdrow;
        public DataRow row;
        public MySqlDataReader reader;
        private Config config;
        public MySqlConnection conn = new MySqlConnection();
        public MySqlConnection mysqlcon = new MySqlConnection();
        public MySqlCommand command = new MySqlCommand();
        public MySqlCommandBuilder builder = new MySqlCommandBuilder();
        private MySqlDataAdapter adapter = new MySqlDataAdapter();
        public DataSet ds = new DataSet();
        public DataTable update;
        private string _host;
        private string _username;
        private string _pass;
        public string _database;
        private int _port;
    }
}
